package net.zoneland.knowledge.dao;

import net.zoneland.knowledge.controller.bean.SanHuiYiKeVo;
import net.zoneland.knowledge.model.SanHuiYiKe;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 三会一课数据接口.
 * @author zx
 */
@Mapper
public interface SanHuiYiKeMapper {

    /**
     * 查询列表.
     * @param sanHuiYiKeVo 对象
     * @return 集合
     */
    @Select("<script>" +
        "SELECT ID, ISSUE_RECORD, MEETING_NAME, MEETING_PLACE, MEETING_STARTTIME, MEETING_STATUS, UPPER(ORG_UUID) as ORG_UUID, PLAN_NAME, TYPE_RECORD,COMPANY_NAME " +
        "FROM AITK_SAN_HUI_YI_KE " +
        "WHERE " +
        "<foreach collection='meetingNames' item='item' open='(' separator='or' close=')'>" +
        " MEETING_NAME like '%${item}%' or ISSUE_RECORD like '%${item}%' </foreach> " +
        " <if test='orgUuids != null and orgUuids.size > 0'> " +
        " AND UPPER(ORG_UUID) in " +
        "<foreach collection='orgUuids' index='index' item='item' open='(' separator=',' close=')'>" +
        " UPPER(#{item}) </foreach> " +
        "</if>" +
        " <if test='meetingStartTime != null and meetingEndTime != null '> " +
        " AND " +
        "MEETING_STARTTIME BETWEEN to_date('${meetingStartTime} 00:00:00', 'YYYY-MM-dd HH24:mi:ss') AND to_date('${meetingEndTime} 23:59:59', 'YYYY-MM-dd HH24:mi:ss')" +
        "</if>" +
        " <if test='companyName != null and companyName != \"\" '> AND COMPANY_NAME like '%${companyName}%' </if>" +
        " ORDER BY ORG_UUID DESC, MEETING_STARTTIME DESC " +
        "</script>"
    )
    @Results({
        @Result(column="ID", property="id", jdbcType= JdbcType.VARCHAR, id=true),
        @Result(column="ISSUE_RECORD", property="issueRecord", jdbcType=JdbcType.VARCHAR),
        @Result(column="MEETING_NAME", property="meetingName", jdbcType=JdbcType.VARCHAR),
        @Result(column="MEETING_PLACE", property="meetingPlace", jdbcType=JdbcType.VARCHAR),
        @Result(column="MEETING_STARTTIME", property="meetingStartTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="MEETING_STATUS", property="meetingStatus", jdbcType=JdbcType.VARCHAR),
        @Result(column="ORG_UUID", property="orgUuid", jdbcType=JdbcType.VARCHAR),
        @Result(column="PLAN_NAME", property="planName", jdbcType=JdbcType.VARCHAR),
        @Result(column="TYPE_RECORD", property="typeRecord", jdbcType=JdbcType.VARCHAR),
        @Result(column="COMPANY_NAME", property="companyName", jdbcType=JdbcType.VARCHAR)
    })
    List<SanHuiYiKe> search(final SanHuiYiKeVo sanHuiYiKeVo);

    /**
     * 查询列表.
     * @param sanHuiYiKeVo 对象
     * @return 集合
     */
    @Select("<script>" +
        "SELECT ID, ISSUE_RECORD, MEETING_NAME, MEETING_PLACE, MEETING_STARTTIME, MEETING_STATUS, ORG_UUID, PLAN_NAME, TYPE_RECORD,COMPANY_NAME " +
        "FROM AITK_SAN_HUI_YI_KE " +
        "WHERE 1=1 " +
        " <if test='orgUuids != null and orgUuids.size > 0'> " +
        " AND ORG_UUID in " +
        "<foreach collection='orgUuids' index='index' item='item' open='(' separator=',' close=')'>" +
        " #{item} </foreach> " +
        "</if>" +
        " <if test='meetingStartTime != null and meetingEndTime != null '> " +
        " AND " +
        "MEETING_STARTTIME BETWEEN to_date('${meetingStartTime} 00:00:00', 'YYYY-MM-dd HH24:mi:ss') AND to_date('${meetingEndTime} 23:59:59', 'YYYY-MM-dd HH24:mi:ss')" +
        "</if>" +
        " <if test='companyName != null and companyName != \"\" '> AND COMPANY_NAME like '%${companyName}%' </if>" +
        " ORDER BY ORG_UUID DESC, MEETING_STARTTIME DESC " +
        "</script>"
    )
    @Results({
        @Result(column="ID", property="id", jdbcType= JdbcType.VARCHAR, id=true),
        @Result(column="ISSUE_RECORD", property="issueRecord", jdbcType=JdbcType.VARCHAR),
        @Result(column="MEETING_NAME", property="meetingName", jdbcType=JdbcType.VARCHAR),
        @Result(column="MEETING_PLACE", property="meetingPlace", jdbcType=JdbcType.VARCHAR),
        @Result(column="MEETING_STARTTIME", property="meetingStartTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="MEETING_STATUS", property="meetingStatus", jdbcType=JdbcType.VARCHAR),
        @Result(column="ORG_UUID", property="orgUuid", jdbcType=JdbcType.VARCHAR),
        @Result(column="PLAN_NAME", property="planName", jdbcType=JdbcType.VARCHAR),
        @Result(column="TYPE_RECORD", property="typeRecord", jdbcType=JdbcType.VARCHAR),
        @Result(column="COMPANY_NAME", property="companyName", jdbcType=JdbcType.VARCHAR)
    })
    List<SanHuiYiKe> searchList(final SanHuiYiKeVo sanHuiYiKeVo);

    /**
     * 查询.
     * @param id id
     * @return 对象
     */
    @Select({
            "SELECT ID, ISSUE_RECORD, MEETING_NAME, MEETING_PLACE, MEETING_STARTTIME, MEETING_STATUS, ORG_UUID, PLAN_NAME, TYPE_RECORD,COMPANY_NAME ",
            "FROM AITK_SAN_HUI_YI_KE ",
            "WHERE ",
            " ID = #{id, jdbcType= VARCHAR}"
    })
    @Results(id = "SanHuiYiKeMap", value = {
            @Result(column="ID", property="id", jdbcType= JdbcType.VARCHAR, id=true),
            @Result(column="ISSUE_RECORD", property="issueRecord", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_NAME", property="meetingName", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_PLACE", property="meetingPlace", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_STARTTIME", property="meetingStartTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="MEETING_STATUS", property="meetingStatus", jdbcType=JdbcType.VARCHAR),
            @Result(column="ORG_UUID", property="orgUuid", jdbcType=JdbcType.VARCHAR),
            @Result(column="PLAN_NAME", property="planName", jdbcType=JdbcType.VARCHAR),
            @Result(column="TYPE_RECORD", property="typeRecord", jdbcType=JdbcType.VARCHAR),
            @Result(column="COMPANY_NAME", property="companyName", jdbcType=JdbcType.VARCHAR)
    })
    SanHuiYiKe getInfoById(final String id);

    /**
     * 根据关键词查询列表.
     * @param sanHuiYiKeVo 对象中orgUuids在使用时，存在大小写敏感问题，需要转换处理
     * @return 集合
     */
    @Select({
            "<script>" +
            "SELECT ID, ISSUE_RECORD, MEETING_NAME, MEETING_PLACE, MEETING_STARTTIME, MEETING_STATUS, ORG_UUID, PLAN_NAME, TYPE_RECORD,COMPANY_NAME " +
            "FROM AITK_SAN_HUI_YI_KE " +
            "WHERE MEETING_STARTTIME >= #{nowDate, jdbcType=TIMESTAMP} and " +
            "<foreach collection='sanHuiYiKeVo.meetingNames' item='item' open='(' separator='or' close=')'>" +
            " MEETING_NAME like '%'||#{item}||'%' or ISSUE_RECORD like '%'||#{item}||'%' </foreach> " +
            " <if test='sanHuiYiKeVo.orgUuids != null and sanHuiYiKeVo.orgUuids.size > 0'> " +
            " AND UPPER(ORG_UUID) in " +
            "<foreach collection='sanHuiYiKeVo.orgUuids' index='index' item='item' open='(' separator=',' close=')'>" +
            " UPPER(#{item}) </foreach> " +
            "</if>" +
            " <if test='sanHuiYiKeVo.meetingStartTime != null and meetingEndTime != null '> " +
            " AND " +
            "MEETING_STARTTIME BETWEEN to_date(#{sanHuiYiKeVo.meetingStartTime}||' 00:00:00', 'YYYY-MM-dd HH24:mi:ss') AND to_date(#{sanHuiYiKeVo.meetingEndTime}||' 23:59:59', 'YYYY-MM-dd HH24:mi:ss')" +
            "</if>" +
            " ORDER BY MEETING_STARTTIME DESC " +
            "</script>"
    })
    @ResultMap(value = {"SanHuiYiKeMap"})
    List<SanHuiYiKe> searchByOrgUuidAndMeetingNames(@Param("sanHuiYiKeVo") final SanHuiYiKeVo sanHuiYiKeVo, @Param("nowDate") final LocalDateTime nowDate);

    /**
     * 根据支部查询列表.
     * @param sanHuiYiKeVo 对象中orgUuids在使用时，存在大小写敏感问题，需要转换处理
     * @return 集合
     */
    @Select({
            "<script>" +
            "SELECT ID, ISSUE_RECORD, MEETING_NAME, MEETING_PLACE, MEETING_STARTTIME, MEETING_STATUS, ORG_UUID, PLAN_NAME, TYPE_RECORD,COMPANY_NAME " +
            "FROM AITK_SAN_HUI_YI_KE " +
            "WHERE MEETING_STARTTIME >= #{nowDate, jdbcType=TIMESTAMP} and UPPER(ORG_UUID) in " +
            "<foreach collection='sanHuiYiKeVo.orgUuids' index='index' item='item' open='(' separator=',' close=')'>" +
            " UPPER(#{item})</foreach> " +
            " ORDER BY MEETING_STARTTIME DESC " +
            "</script>"
    })
    @ResultMap(value = {"SanHuiYiKeMap"})
    List<SanHuiYiKe> searchByOrgUuid(@Param("sanHuiYiKeVo") final SanHuiYiKeVo sanHuiYiKeVo, @Param("nowDate") final LocalDateTime nowDate);

    /**
     * 根据id查询会议.
     * @param ids ids
     * @return 对象
     */
    @Select({
            "<script>" +
            "SELECT ID, ISSUE_RECORD, MEETING_NAME, MEETING_PLACE, MEETING_STARTTIME, MEETING_STATUS, ORG_UUID, PLAN_NAME, TYPE_RECORD,COMPANY_NAME ",
            "FROM AITK_SAN_HUI_YI_KE ",
            "WHERE ",
            " ID in ",
            "<foreach collection='ids' index='index' item='item' open='(' separator=',' close=')'>",
            " #{item} </foreach> ",
            "</script>"
    })
    @ResultMap(value = {"SanHuiYiKeMap"})
    List<SanHuiYiKe> searchByIds(@Param("ids") final String[] ids);
}
